﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections.Specialized;
using System.Text;

namespace ServeNHL.Models
{
    public class eventViewModel
    {
        public EVENT eventItem { get; set; }
        public EVENT_CAT eventCat { get; set; }
        public EVENT_SIGNUP eventSignUp { get; set; }
        public List<VCLViewModel> VCLViewModels { get; set; }


        public static List<EVENT_CAT> getEventCatForDropdown()
        {
            var cat = new List<EVENT_CAT>();
            using (var dbconn = new fclick_dbEntities())
            {
                // cat = dbconn.VOLUNTEER_CONTROL_LIST.Where(v => v.STATUS == "Active").ToList();
                cat = (from ec in dbconn.EVENT_CAT
                       select ec).ToList();

            }
            return cat;
        }

        //returns a list of all event view models filtered by status as active
        public List<eventViewModel> getUpcomingEvents()
        {
            var eventList = new List<eventViewModel>();



            using (var dbconn = new fclick_dbEntities())
            {

                var query = dbconn.EVENTs.Join(dbconn.EVENT_CAT, e => e.EVENT_CAT_ID, ec => ec.EVENT_CAT_ID, (e, ec) => new { e, ec }).
                     Where(e => e.e.STATUS == "Active").
                     Select(m => new eventViewModel

                     {
                         eventCat = m.ec,
                         eventItem = m.e,


                     });

                eventList = query.ToList();

            }
            return eventList;
        }

        /// <summary>
        /// Given an ID, returns the event object associated with it.
        /// </summary>
        /// <param name="id">ID to search</param>
        /// <returns>Event item</returns>
        public eventViewModel getEvent(int id)
        {
            var events = new eventViewModel();
            using (var dbconn = new fclick_dbEntities())
            {
                var eventQuery = dbconn.EVENTs.Where(e => e.EVENT_ID == id).FirstOrDefault();
                events.eventItem = eventQuery;

            }

            return events;
        }

        /// <summary>
        /// Searches the events given a string, and returns all containing matches
        /// </summary>
        /// <param name="eSearch">String to search</param>
        /// <returns>List of events which contain the search string in their name.</returns>
        public List<EVENT> getEvent(string eSearch)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var sw = from s in dbconn.EVENTs select s;
                sw = sw.Where(t => t.NAME.Contains(eSearch));
                return sw.ToList();


                // var query = from e in EVENTs
            }
        } // End of public List<EVENT> getEvent(string eSearch)

        /// <summary>
        /// Searches the events given a string, start date, end date, and returns all containing matches
        /// </summary>
        /// <param name="eSearch">String to search</param>
        /// <param name="startDate">Start date to search</param>
        /// <param name="endDate">End date to search</param>
        /// <returns>List of events containg the given string in their name, between the start and end date provided.</returns>
        public List<eventViewModel> getEvent(string eSearch, DateTime startDate, DateTime endDate)
        {
            /*   using (var dbconn = new fclick_dbEntities())
               {
                   var sw = from s in dbconn.EVENTs select s;

                   sw = sw.Where(t => t.NAME.Contains(eSearch)
                   && t.START_DATE >= startDate
                   && t.END_DATE <= endDate);
                   return sw.ToList();
               }*/
            var events = new List<eventViewModel>();
            using (var dbconn = new fclick_dbEntities())
            {


                var query = dbconn.EVENTs.Join(dbconn.EVENT_CAT, e => e.EVENT_CAT_ID, ec => ec.EVENT_CAT_ID, (e, ec) => new { e, ec }).
                       Where(e => e.e.NAME.Contains(eSearch)
                   && e.e.START_DATE >= startDate
                   && e.e.END_DATE <= endDate).
                       Select(m => new eventViewModel

                       {
                           eventCat = m.ec,
                           eventItem = m.e,


                       });
          return  events = query.ToList();
               
             }

        } // End of public List<EVENT> getEvent(string eSearch, DateTime startDate, DateTime endDate)

        /// <summary>
        /// Given a start date and end date, returns all events between them
        /// </summary>
        /// <param name="startDate">Start date to search</param>
        /// <param name="endDate">End date to search</param>
        /// <returns>List of events between the start and end date.</returns>
        public List<eventViewModel> getEvents(DateTime startDate, DateTime endDate)
        {
            var events = new List<eventViewModel>();
            using (var dbconn = new fclick_dbEntities())
            {


                var query = dbconn.EVENTs.Join(dbconn.EVENT_CAT, e => e.EVENT_CAT_ID, ec => ec.EVENT_CAT_ID, (e, ec) => new { e, ec }).
                       Where(e => e.e.START_DATE >= startDate
                   && e.e.END_DATE <= endDate).
                       Select(m => new eventViewModel

                       {
                           eventCat = m.ec,
                           eventItem = m.e,


                       });
                return events = query.ToList();
            }
        } // End of public List<EVENT> getEvents(DateTime startDate, DateTime endDate)


        /// <summary>
        /// Given an event, returns a list of users who signed up for it.
        /// </summary>
        /// <param name="e">Event to search for.</param>
        /// <returns>List of users.</returns>
        public List<USER> getEventUsers(EVENT e)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from es in dbconn.EVENT_SIGNUP
                             where es.EVENT_ID == e.EVENT_ID
                             join eu in dbconn.USERs on es.USER_ID equals eu.USER_ID
                             select eu).ToList();
                return query;
            }
        }// End of public List<USER> getEventUsers(EVENT e)

        /// <summary>
        /// Given an event ID, returns a list of users who signed up for it.
        /// </summary>
        /// <param name="eID">Event ID to search for.</param>
        /// <returns>List of users.</returns>
        public List<USER> getEventUsers(int eID)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from es in dbconn.EVENT_SIGNUP
                             where es.EVENT_ID == eID
                             join eu in dbconn.USERs on es.USER_ID equals eu.USER_ID
                             select eu).ToList();
                return query;




            }


        } // End of public List<USER> getEventUsers(int eID)

        /// <summary>
        /// Takes a search term and finds a username containing that term
        /// </summary>
        /// <param name="name">Name to search for</param>
        /// <returns>List of users containing the search term</returns>
        public List<USER> getUsers(string name)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from u in dbconn.USERs
                             where u.USERNAME.Contains(name)
                             select u).ToList();
                return query;
            }
        } // End of public List<USER> getUsers(string name)



        /// <summary>
        /// Takes a user object and returns a list of all events they are signed up for (On and after today, this part can be removed if we want)
        /// </summary>
        /// <param name="name">User to check events</param>
        /// <returns>List of events user has signed up for</returns>
        public List<EVENT> userEvents(USER name)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from e in dbconn.EVENT_SIGNUP
                             where e.USER_ID == name.USER_ID
                             join eu in dbconn.EVENTs on e.EVENT_ID equals eu.EVENT_ID
                             where eu.START_DATE >= DateTime.Now
                             select eu).ToList();
                return query;
            }
        } // End public List<EVENT> userEvents(USER name)




        /// <summary>
        /// Takes a user object and returns a list of all events they are signed up for (On and after today, this part can be removed if we want)
        /// </summary>
        /// <param name="ID">User ID to check events</param>
        /// <returns>List of events user has signed up for</returns>
        /// ben's changing this to a view model
        public List<eventViewModel> userEvents(int ID)
        {
            using (var dbconn = new fclick_dbEntities())
            {

                /*
                var query = (from e in dbconn.EVENT_SIGNUP
                             where e.USER_ID == ID
                             join eu in dbconn.EVENTs on e.EVENT_ID equals eu.EVENT_ID
                             where eu.START_DATE >= DateTime.Now
                             select eu).ToList();
*/

                var evtquery = dbconn.EVENT_SIGNUP.Join(dbconn.EVENTs, es => es.EVENT_ID, e => e.EVENT_ID, (es, e) => new { es, e })
                     .Where(es => es.es.USER_ID == ID && es.e.STATUS == "Active")
                     .Select(vm => new eventViewModel
                     {
                         eventItem = vm.e,
                         eventSignUp = vm.es,
                         eventCat = vm.e.EVENT_CAT,

                     });
                return evtquery.ToList();
            }
        } // End public List<EVENT> userEvents(int ID)


        /// <summary>
        /// Removes Event Category based on its ID
        /// </summary>
        /// <param name="ID">Event Category ID</param>
        /// <returns>True if it was removed.</returns>
        public Boolean RemoveEventCategory(int ID)
        {
            Boolean removed = false;

            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from ecat in dbconn.EVENT_CAT
                             where ecat.EVENT_CAT_ID == ID
                             select ecat).FirstOrDefault();
                dbconn.EVENT_CAT.Remove(query);
                removed = true;
            }
            return removed;
        }


        /// <summary>
        /// Searches for an Event Category based on a containing the search string
        /// </summary>
        /// <param name="eventCategoryName">Name to search for.</param>
        /// <returns></returns>
        public List<EVENT_CAT> SearchEventCategory(string eventCategoryName)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from ecat in dbconn.EVENT_CAT
                             where ecat.EVENT_CAT_NAME.Contains(eventCategoryName)
                             select ecat).ToList();
                return query;
            }
        }


        /// <summary>
        /// Removes Event Category
        /// </summary>
        /// <param name="ID">Event Category ID</param>
        /// <returns>True if it was removed.</returns>
        public Boolean RemoveEventCategory(EVENT_CAT ec)
        {
            Boolean removed = false;

            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from ecat in dbconn.EVENT_CAT
                             where ecat.EVENT_CAT_ID == ec.EVENT_CAT_ID
                             select ecat).FirstOrDefault();
                dbconn.EVENT_CAT.Remove(query);
                removed = true;
            }
            return removed;
        }


        /// <summary>
        /// Removes a user from all of their signed up events.
        /// </summary>
        /// <param name="user">User to remove from all signed up events.</param>
        public void RemoveFromAllEvents(USER user)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from es in dbconn.EVENT_SIGNUP
                             where es.USER_ID == user.USER_ID
                             select es).ToList();
                foreach (EVENT_SIGNUP e in query)
                {
                    dbconn.EVENT_SIGNUP.Remove(e);
                }
                dbconn.SaveChanges();
            }
        }

        /// <summary>
        /// Remove event based on its ID
        /// </summary>
        /// <param name="id"></param>
        public void DeleteEvent(int id)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from e in dbconn.EVENTs
                             where e.EVENT_ID == id
                             select e).FirstOrDefault();
                dbconn.EVENTs.Remove(query);
                dbconn.SaveChanges();
            }
        }


        /// <summary>
        /// Removes an event
        /// </summary>
        /// <param name="e">Event to remove</param>
        public void DeleteEvent(EVENT e)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from ev in dbconn.EVENTs
                             where ev.EVENT_ID == e.EVENT_ID
                             select ev).FirstOrDefault();
                dbconn.EVENTs.Remove(query);
                dbconn.SaveChanges();
            }
        }








        /// <summary>
        /// Given an event ID, returns a list of volunteers
        /// </summary>
        /// <param name="eventItem"></param>
        /// <returns></returns>
        // public EVENT eventLookup(int ID)
        //{
        //  using (var dbconn = new fclick_dbEntities())
        //{
        //  var query = ();
        //}
        // }

        //Calling SaveEvent in event.partial
        public eventViewModel SaveEvent(eventViewModel eventItem)
        {
         eventItem.eventItem.SaveEvent(eventItem.eventItem);
       
         


        //save each part of view model

            return eventItem;

            //Possibly return a view here?
        }// end of save 

        public List<eventViewModel> getEventsVM(string sSearch)
        {
            using (var dbconn = new fclick_dbEntities())
            {
                var query = (from s in dbconn.EVENTs
                             where s.NAME.Contains(sSearch)
                             select new eventViewModel
                             {
                                 eventItem = s
                             });
                return query.ToList();
            }

        }

    }
}